package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select ci.chapter_name 'name',\n" +
            "       count(ucp.id) 'num',\n" +
            "       sum(ucp.position_sec) 'time',\n" +
            "       count(distinct ucp.user_id) 'count',\n" +
            "       sum(ucp.position_sec)/count(distinct ucp.user_id) 'avgtime'\n" +
            "from user_chapter_process ucp\n" +
            "left join chapter_info ci on ci.id = ucp.chapter_id\n" +
            "group by ci.chapter_name")
    List<Vo1> getList1();

    @Select("with a as(\n" +
            "select\n" +
            "    case\n" +
            "    when score<60 then '[0,60)'\n" +
            "    when score>=60 and score<80 then '[60,80)'\n" +
            "    when score>=80 and score<90 then '[80,90)'\n" +
            "    when score>=90 and score<95 then '[90,95)'\n" +
            "    when score>=95 and score<=100 then '[95,100]'\n" +
            "    end 'name'\n" +
            "from test_exam)select a.name,count(*) 'value' from a group by a.name")
    List<Vo> getList2();

    @Select("select bp.name,sum(oi.final_amount) `value`  from order_info oi\n" +
            "                                                      left join  base_province bp on oi.province_id = bp.id\n" +
            "group by bp.name")
    List<Vo> getList3();

    @Select("select sum(final_amount)  from order_info")
    Double getList4();

    @Select("select bs.source_site `name`,sum(oi.final_amount) `price`,\n" +
            "       count(distinct  oi.user_id) `num`,count(oi.id) 'count'\n" +
            "from  order_info oi\n" +
            "          left join  order_detail od on oi.id = od.order_id\n" +
            "          left join course_info ci on od.course_id = ci.id\n" +
            "          left join chapter_info c on ci.id = c.course_id\n" +
            "          left join video_info vi on c.id = vi.chapter_id\n" +
            "          left join base_source bs on bs.id = vi.video_source_id\n" +
            "group by  bs.source_site")
    List<Vo2> getList5();


    @Select("select count(*) from order_info where date(create_time)>'2024-04-23'")
    Double getList6();


    @Select("select ci.course_name `name`,sum(oi.final_amount) `value`\n" +
            "from  order_info oi\n" +
            "          left join  order_detail od on oi.id = od.order_id\n" +
            "          left join course_info ci on od.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo> getList7();

    @Select("select hour(create_time) `name`,count(*) `value`  from  user_chapter_process group by hour(create_time) order by  hour(create_time)\n")
    List<Vo> getList8();
}
